﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Reflection
Imports System.ComponentModel

Public Class DataAccess
    
    Public Shared vcConnectionI As String = ConfigurationManager.AppSettings("cnnStringI")
    Public Shared vcConnectionII As String = ConfigurationManager.AppSettings("cnnStringII")

    Function fConnection(ByVal cnnString As String) As String
        Select Case cnnString
            Case "cnnStringI"
                Return vcConnectionI
            Case "cnnStringII"
                Return vcConnectionII
            Case Else
                Return vcConnectionI
        End Select
    End Function

    Public Function fSelDst(ByVal vCon As SqlConnection, ByVal vTrx As SqlTransaction, ByVal vSP As String, Optional ByVal vObj As Object = Nothing) As DataSet
        Using dap As New SqlDataAdapter(vSP, vCon)
            Using dst As New DataSet
                With dap.SelectCommand
                    .Transaction = vTrx
                    .CommandTimeout = 2000
                    .CommandType = CommandType.StoredProcedure
                End With
                If Not vObj Is Nothing Then CrearParametros(dap.SelectCommand, vObj)
                dap.Fill(dst, vSP)
                Return (dst)
            End Using
        End Using
    End Function

    Public Function fSelDrd(ByVal vCon As SqlConnection, ByVal vTrx As SqlTransaction, ByVal vSP As String, Optional ByVal vObj As Object = Nothing) As SqlDataReader
        Dim cmd As New SqlCommand(vSP, vCon)
        Dim drd As SqlDataReader
        cmd.Transaction = vTrx
        cmd.CommandTimeout = 2000
        cmd.CommandType = CommandType.StoredProcedure
        If Not vObj Is Nothing Then CrearParametros(cmd, vObj)
        drd = cmd.ExecuteReader(CommandBehavior.SingleResult)
        Return (drd)
    End Function

    Public Function fSelRow(ByVal vCon As SqlConnection, ByVal vTrx As SqlTransaction, ByVal vSP As String, Optional ByVal vObj As Object = Nothing) As SqlDataReader
        Using cmd As New SqlCommand(vSP, vCon)
            Dim drd As SqlDataReader
            cmd.Transaction = vTrx
            cmd.CommandType = CommandType.StoredProcedure
            If Not vObj Is Nothing Then CrearParametros(cmd, vObj)
            drd = cmd.ExecuteReader(CommandBehavior.SingleRow Or CommandBehavior.CloseConnection)
            Return (drd)
        End Using
    End Function

    Public Function fSelVal(ByVal vCon As SqlConnection, ByVal vTrx As SqlTransaction, ByVal vSP As String, Optional ByVal vObj As Object = Nothing) As Object
        Dim cmd As New SqlCommand(vSP, vCon)
        Dim val As Object
        cmd.Transaction = vTrx
        cmd.CommandType = CommandType.StoredProcedure
        If Not vObj Is Nothing Then CrearParametros(cmd, vObj)
        val = cmd.ExecuteScalar
        Return (val)
    End Function

    Public Function fMan(ByVal vCon As SqlConnection, ByVal vTrx As SqlTransaction, ByVal vSP As String, ByVal vObj As Object) As Integer
        Using cmd As New SqlCommand(vSP, vCon)
            Dim n As Integer = -1
            cmd.Transaction = vTrx
            cmd.CommandType = CommandType.StoredProcedure
            CrearParametros(cmd, vObj)
            n = cmd.ExecuteNonQuery
            If n <= 0 Then Throw New Exception("No se pudo realizar la operacion")
            n = cmd.Parameters(0).Value
            Return (n)
        End Using
    End Function

    Private Sub CrearParametros(ByRef vCmd As SqlCommand, ByVal vObj As Object)
        SqlCommandBuilder.DeriveParameters(vCmd)
        Dim I As Integer
        Dim Propiedad() As PropertyInfo = vObj.GetType.GetProperties()
        For I = 0 To Propiedad.Length - 1
            Dim Valor As Object = Propiedad(I).GetValue(vObj, Nothing)
            Dim da As DescriptionAttribute = Propiedad(I).GetCustomAttributes(False)(0)
            Dim Valido As Boolean
            If Not Valor Is Nothing Then
                Select Case Valor.GetType.ToString
                    Case "System.String"
                        Valido = Valor <> ""
                    Case "System.Int8", "System.Int16", "System.Int32", "System.Decimal"
                        Valido = Valor <> -1
                    Case "System.DateTime"
                        Valido = Valor <> #12:00:00 AM#
                End Select
            Else
                Valido = False
            End If
            If Valido Then
                vCmd.Parameters("@" + da.Description).Value = Valor
            End If
        Next
    End Sub


End Class
